// AdminStatisticsMapper.java
package com.scuec.groupfive.mapper;

import com.scuec.groupfive.vo.AdminUserStatisticsVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface AdminStatisticsMapper {

    @Select("SELECT " +
            "  u.user_id AS userId, " +
            "  u.username AS username, " +
            "  (SELECT COUNT(*) FROM answer_records ar WHERE ar.user_id = u.user_id) AS totalAnswers, " +
            "  (SELECT ROUND( " +
            "       COUNT(DISTINCT CASE WHEN ar.is_correct = 1 THEN ar.question_id END) * 100.0 / " +
            "       COUNT(DISTINCT ar.question_id), " +
            "     2 " +
            "   ) " +
            "   FROM answer_records ar " +
            "   WHERE ar.user_id = u.user_id " +
            "     AND ar.question_type IN ('single_choice','fill_in_the_blank') " +
            "  ) AS avgAccuracyRate, " +  // 关键修改：使用题目去重统计
            "  (SELECT COUNT(*) FROM past_exam_records per WHERE per.user_id = u.user_id) " +
            "  + (SELECT COUNT(*) FROM mock_exams me WHERE me.user_id = u.user_id) AS totalExams, " +
            "  (SELECT ROUND(AVG(per.score), 2) " +
            "   FROM past_exam_records per " +
            "   WHERE per.user_id = u.user_id " +
            "  ) AS avgPastExamScore " +
            "FROM users u " +
            "WHERE u.user_id = #{userId}")
    AdminUserStatisticsVO getUserStatistics(@Param("userId") Long userId);
}